﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using System.Data.SQLite;

namespace StudentManager
{
  

  
    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }

    public class Log
    {
        public int Id { get; set; }
        public string Action { get; set; }
        public DateTime Timestamp { get; set; }
    }
      public class School
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public List<Class> Classes { get; set; }
    }
    public class Class
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public List<Student> stu { get; set; }
    }

    public class DatabaseManager
    {
        private SQLiteConnection connection;

        public DatabaseManager()
        {
            connection = new SQLiteConnection("Data Source=student.db;Version=3;");

            connection.Open();
            InitializeDatabase();
        }

     

        public List<School> GetSchools()
        {
            string query = "SELECT * FROM Schools";


            DataTable schoolsTable = ExecuteQuery(query);


            List<School> schools = new List<School>();

            foreach (DataRow row in schoolsTable.Rows)
            {
                School school = new School
                {
                    Id = Convert.ToInt32(row["Id"]),
                    Name = row["Name"].ToString()
                };

                school.Classes = GetClassesBySchoolId(school.Id);
                schools.Add(school);
            }

            return schools;
        }
        private void InitializeDatabase()
        {
            string createSchoolTableQuery = "CREATE TABLE IF NOT EXISTS Schools (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT)";
            ExecuteNonQuery(createSchoolTableQuery);

         


            string createClassTableQuery = "CREATE TABLE IF NOT EXISTS Classes (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, SchoolId INTEGER)";
            ExecuteNonQuery(createClassTableQuery);

            // 创建学生表
            string createStudentTableQuery = "CREATE TABLE IF NOT EXISTS stu (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Age INTEGER, ClassId INTEGER)";
            ExecuteNonQuery(createStudentTableQuery);

            // 创建日志表
            string createLogTableQuery = "CREATE TABLE IF NOT EXISTS Logs (Id INTEGER PRIMARY KEY AUTOINCREMENT, Action TEXT, Timestamp DATETIME DEFAULT CURRENT_TIMESTAMP)";
            ExecuteNonQuery(createLogTableQuery);
        }
       

        public void DeleteSchool(int schoolId)
        {
            string query = $"DELETE FROM Schools WHERE Id = {schoolId}";
            ExecuteNonQuery(query);
        }

        public List<Class> GetClassesBySchoolId(int schoolId)
        {
            string query = $"SELECT * FROM Classes WHERE SchoolId = {schoolId}";
            DataTable classesTable = ExecuteQuery(query);
            List<Class> classes = new List<Class>();
            foreach (DataRow row in classesTable.Rows)
            {
                Class classObj = new Class
                {
                    Id = Convert.ToInt32(row["Id"]),
                    Name = row["Name"].ToString()
                };

                classObj.stu = getstuByClassId(classObj.Id);
                classes.Add(classObj);
            }

            return classes;
        }
        public void AddSchool(School school)
        {
                              string query = $"INSERT INTO Schools (Name) VALUES ('{school.Name}')";

            ExecuteNonQuery(query);
        }

        public void adClass(Class classObj, int schoolId)
        {
            string query = $"INSERT INTO Classes (Name, SchoolId) VALUES ('{classObj.Name}', {schoolId})";
                               ExecuteNonQuery(query);
        }

    

      
        public void upclass(Class classObj)
        {
                      string query = $"UPDATE Classes SET Name = '{classObj.Name}' WHERE Id = {classObj.Id}";
            ExecuteNonQuery(query);
        }
        public void upschool(School school)
        {
            string query = $"UPDATE Schools SET Name = '{school.Name}' WHERE Id = {school.Id}";

                   ExecuteNonQuery(query);
        }
        public void declass(int classId)
        {
                  string query = $"DELETE FROM Classes WHERE Id = {classId}";

            ExecuteNonQuery(query);
        }
        public List<Student> getstuByClassId(int classId)
        {
            string query = $"SELECT * FROM stu WHERE ClassId = {classId}";

                           DataTable stuTable = ExecuteQuery(query);


 
            List<Student> stu = new List<Student>();
          
            foreach (DataRow row in stuTable.Rows)
            {
                Student student = new Student
                {
                    Id = Convert.ToInt32(row["Id"]),
                    Name = row["Name"].ToString(),
                    Age = Convert.ToInt32(row["Age"])
                };

                stu.Add(student);
            }

            return stu;
        }

        public void adstu(Student student, int classId)
        {
            string query = $"INSERT INTO stu (Name, Age, ClassId) VALUES ('{student.Name}', {student.Age}, {classId})";
            ExecuteNonQuery(query);
        }

     
  

     
        public void AddLog(string action)
        {
            string query = $"INSERT INTO Logs (Action) VALUES ('{action}')";
            ExecuteNonQuery(query);
        }

        private DataTable ExecuteQuery(string query)
        {
            SQLiteCommand command = new SQLiteCommand(query, connection);
            SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            return dataTable;
        }
        public void destu(int studentId)
        {
            string query = $"DELETE FROM stu WHERE Id = {studentId}";
            ExecuteNonQuery(query);
        }
        public void upstu(Student student)
        {
            string query = $"UPDATE stu SET Name = '{student.Name}', Age = {student.Age} WHERE Id = {student.Id}";
            ExecuteNonQuery(query);
        }
        private void ExecuteNonQuery(string query)
        {
            SQLiteCommand command = new SQLiteCommand(query, connection);
            command.ExecuteNonQuery();
        }
        public List<Log> GetLogs()
        {
            string query = "SELECT * FROM Logs";
            DataTable logsTable = ExecuteQuery(query);
            List<Log> logs = new List<Log>();

            foreach (DataRow row in logsTable.Rows)
            {
                Log log = new Log
                {
                    Id = Convert.ToInt32(row["Id"]),
                    Action = row["Action"].ToString(),
                    Timestamp = Convert.ToDateTime(row["Timestamp"])
                };

                logs.Add(log);
            }

            return logs;
        }
    }
   

    public class Program
    {
        private static DatabaseManager databaseManager;

        static void Main(string[] args)
        {
            databaseManager = new DatabaseManager();

            // 创建学校、班级和学生示例数据
            School school = new School { Name = "学校1" };
            databaseManager.AddSchool(school);

            Class classObj = new Class { Name = "班级1" };
            databaseManager.adClass(classObj, school.Id);
            Student student = new Student { Name = "学生1", Age = 18 };
            databaseManager.adstu(student, classObj.Id);

            // 输出学校、班级和学生信息
            List<School> schools = databaseManager.GetSchools();
            foreach (School s in schools)
            {
                Console.WriteLine($"学校ID: {s.Id}, 名称: {s.Name}");

                foreach (Class c in s.Classes)
                {
                    Console.WriteLine($"\t班级ID: {c.Id}, 名称: {c.Name}");

                    foreach (Student stu in c.stu)
                    {
                        Console.WriteLine($"\t\t学生ID: {stu.Id}, 姓名: {stu.Name}, 年龄: {stu.Age}");
                    }
                }
            }

            // 添加操作记录
            databaseManager.AddLog("创建学校");

            // 获取操作记录并输出
            List<Log> logs = databaseManager.GetLogs();
            foreach (Log log in logs)
            {
                Console.WriteLine($"日志ID: {log.Id}, 操作: {log.Action}, 时间戳: {log.Timestamp}");
            }

            Console.ReadLine();
        }
    }
}
